package com.msw.et.util;

import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author mashuangwei
 * @date 2019-01-14 18:42
 * @description:
 */
@Slf4j
public class MysqlJdbcUtil {

    // 定义数据库的驱动信息
    private final String dirver = "com.mysql.jdbc.Driver";

    private String prefix = "jdbc:mysql://";

    private String suffix = "?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false";

    // 定义访问数据库的连接
    private Connection connection;
    // 定义sql语句的执行对象
    private PreparedStatement preparedStatement;
    // 定义查询返回的结果集合
    private ResultSet resultSet;

    public MysqlJdbcUtil() {
        try {
            Class.forName(dirver);
            log.info("注册驱动成功！！");
        } catch (ClassNotFoundException e) {
            log.error("注册驱动失败！！");
        }

    }

    // 定义获得数据库的连接
    public Connection getConnection(String url, String username, String password, int port, String database_name) {
        url = prefix + ":" + port + "/" + database_name + suffix;
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            log.error("Connection exception !");
        }
        return connection;
    }

    /**
     * 完成对数据库标的增加删除和修改的操作
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public boolean updateByPreparedStatement(String sql, List<Object> params) throws SQLException {
        boolean flag = false;
        int result = -1;// 表示当用户执行增加删除和修改的操作影响的行数
        int index = 1; // 表示 占位符 ，从1开始
        preparedStatement = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i)); // 填充占位符
            }
        }

        result = preparedStatement.executeUpdate();
        return result > 0 ? true : false;
    }

    /**
     * 查询返回单条记录
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public Map<String, Object> findSimpleResult(String sql, List<Object> params)
            throws SQLException {
        Map<String, Object> map = new HashMap<String, Object>();
        preparedStatement = connection.prepareStatement(sql);
        int index = 1;
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i));
            }
        }
        resultSet = preparedStatement.executeQuery(); // 返回查询结果

        ResultSetMetaData metaData = preparedStatement.getMetaData(); // 获取 结果中，一行所有列的结果
        int cols_len = metaData.getColumnCount(); // 获得列的总数

        while (resultSet.next()) {
            for (int i = 0; i < cols_len; i++) {
                String col_name = metaData.getColumnName(i + 1); // 获得第i列的字段名称
                Object col_value = resultSet.getObject(col_name);// 返回 第i列的内容值
                if (col_value == null) {
                    col_value = "";
                }
                map.put(col_name, col_value);
            }

        }

        return map;
    }

    /**
     * 查询返回多条记录
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> findMoreResult(String sql,
                                                    List<Object> params) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        preparedStatement = connection.prepareStatement(sql);
        int index = 1; // 表示占位符
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i));
            }
        }
        resultSet = preparedStatement.executeQuery(); // 返回查询结果集合
        ResultSetMetaData metaData = resultSet.getMetaData(); // 获得列的结果

        while (resultSet.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            int cols_len = metaData.getColumnCount(); // 获取总的列数
            for (int i = 0; i < cols_len; i++) {
                String col_name = metaData.getColumnName(i + 1); // 获取第 i列的字段名称
                // ,列计算从1开始
                Object col_value = resultSet.getObject(col_name); // 获取第i列的内容值
                if (col_value == null) {
                    col_value = "";
                }

                map.put(col_name, col_value);
            }
            list.add(map);
        }

        return list;
    }

    /**
     * 查询返回单个JavaBean(使用java反射机制)
     *
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws Exception
     */
    public <T> T findSimpleRefResult(String sql, List<Object> params,
                                     Class<T> cls) throws Exception {
        T resultObject = null;
        int index = 1; // 占位符
        preparedStatement = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i)); // 填充占位符
            }
        }
        resultSet = preparedStatement.executeQuery(); // 获取查询结果

        ResultSetMetaData metaData = resultSet.getMetaData(); // 获取列的信息
        int cols_len = metaData.getColumnCount(); // 获取总的列数
        while (resultSet.next()) {
            // 通过反射机制创建实例
            resultObject = cls.newInstance(); // java反射机制
            for (int i = 0; i < cols_len; i++) {
                String col_name = metaData.getColumnName(i + 1); // 获取第i列的名称
                Object col_value = resultSet.getObject(col_name); // 获取第i列的值
                if (col_value == null) {
                    col_value = "";
                }
                Field field = cls.getDeclaredField(col_name);
                field.setAccessible(true);// 打开 JavaBean的访问 private权限
                field.set(resultObject, col_value);
            }

        }

        return resultObject;
    }

    /**
     * 查询返回多个JavaBean(通过java反射机制)
     *
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws Exception
     */
    public <T> List<T> findMoreRefResult(String sql, List<Object> params,
                                         Class<T> cls) throws Exception {
        List<T> list = new ArrayList<T>();
        int index = 1; //占位符
        preparedStatement = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i));
            }
        }
        resultSet = preparedStatement.executeQuery(); // 返回查询结果集合

        ResultSetMetaData metaData = resultSet.getMetaData(); // 返回列的信息
        int cols_len = metaData.getColumnCount(); // 结果集中总的列数
        while (resultSet.next()) {
            // 通过反射机制创建一个java实例
            T resultObject = cls.newInstance();
            for (int i = 0; i < cols_len; i++) {
                String col_name = metaData.getColumnName(i + 1); // 获得第i列的名称
                Object col_value = resultSet.getObject(col_name); // 获得第i列的内容
                if (col_value == null) {
                    col_value = "";
                }
                Field field = cls.getDeclaredField(col_name);
                field.setAccessible(true); // 打开JavaBean的访问private权限
                field.set(resultObject, col_value);
            }
            list.add(resultObject);

        }

        return list;
    }

    /**
     * 关闭数据库访问
     *
     * @throws SQLException
     */
    public void releaseConn() throws SQLException {
        if (resultSet != null) {
            resultSet.close();
        }
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }


}

